﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using BOMS.DBUtility;
using System.Configuration;
using System.Data;
using System.Xml;
using BOMS.DATA;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;
using System.Data.Common;
using System.Data.SqlClient;
using BOMS.Utility;

namespace BOMS.ConsoleService.DATA
{

    public class DataUti2
    {
        string DBType = ConfigurationManager.AppSettings["DBType"];
        static string WARN_DATA_DELIM = "@@@@@";
        /// <summary>
        /// 获取所有的设备Model（包括有效和无效的）
        /// </summary>
        /// <returns></returns>
        public List<EquipModel> GetEquipModelList()
        {
           
            string sql = "select ID,ClientHostIP,Port,Mac,Memo,Isvalid from W_Equips";

            DataTable dt = DbHelper.Query(sql).Tables[0];
            List<EquipModel> list = new List<EquipModel>();
            EquipModel model = null;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                model = new EquipModel();
                model.ClientHostIP = dt.Rows[i]["ClientHostIP"].ToString();
                model.Port = dt.Rows[i]["Port"]==DBNull.Value?0:(int)dt.Rows[i]["Port"];
                model.Mac = dt.Rows[i]["Mac"] == DBNull.Value ? "" : dt.Rows[i]["Mac"].ToString(); 
                model.Memo = dt.Rows[i]["Memo"] == DBNull.Value ? "" : dt.Rows[i]["Memo"].ToString();
                model.IsValid = (bool)dt.Rows[i]["IsValid"];
                list.Add(model);
            }
            return list;
        }
        /// <summary>
        /// 获取所有有效的设备Model
        /// </summary>
        /// <param name="IsValid"></param>
        /// <returns></returns>
        public List<EquipModel> GetEquipModelList(bool IsValid)
        {           
            List<EquipModel> list = GetEquipModelList();
            List<EquipModel> validlist = list.FindAll(delegate(EquipModel model) { return model.IsValid = IsValid; });
            return validlist;
        }
        public void SaveClientIP(List<EquipModel> List)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" delete from W_Equips;");
            for (int i = 0; i < List.Count; i++)
            {
                sb.Append(" insert into dbo.W_Equips(ClientHostIp,Port) values('" + List[i].ClientHostIP + "','" + List[i].Port.ToString() + "');");
            }
            DbHelper.ExecuteSql(sb.ToString());
        }
        public static string ImportEquipData(string ip, string mac, DateTime dtm, string xml)
        {
            /*
          DbHelperSQL.connectionString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
          //DbHelperSQL.ExecuteSql("exec sp_sub_LoadLogData '" + EquipMac + "','" + dtm.ToString() + "','" + xmlData + "'");
          SqlParameter[] parms = new SqlParameter[4];
          (parms[0] = new SqlParameter("@Mac", SqlDbType.NVarChar, 40)).Value = mac;
          (parms[1] = new SqlParameter("@IP", SqlDbType.NVarChar, 40)).Value = ip;
          (parms[2] = new SqlParameter("@dtm", SqlDbType.DateTime)).Value = dtm;
          (parms[3] = new SqlParameter("@xmldata", SqlDbType.NVarChar, -1)).Value = xml;
          SqlDataReader dr = DbHelperSQL.RunProcedure("sp_sub_LoadLogData", parms);
          dr.Dispose();
          dr.Close();
          return "OK"; */
            DataUti2 du = new DataUti2();
            return du.ImportEquipDataFromXML(ip, mac, dtm, xml);
        }
        public string ImportEquipDataFromXML(string ip, string mac, DateTime dtm, string xml)
        {
           const int Generation = 2;//2代机
           string sqlBattaryBuf, dataBatteryWarn;
           string sqlBattaryGrpBuf, dataBatteryGrpWarn;
           string dataWarn;
            string sqlUpdateIpAndOther;

            XmlDocument xmlDoc = new XmlDocument();
            
            xmlDoc.LoadXml(xml);
            BuildBattarySQL(Generation, mac, dtm, xmlDoc, out sqlBattaryBuf, out dataBatteryWarn);
            BuildBattaryGrpSQL(Generation, mac, dtm, xmlDoc, out sqlBattaryGrpBuf,out dataBatteryGrpWarn);
            BuildLanUpdateIpSQL(mac, ip, dtm, xmlDoc, out sqlUpdateIpAndOther);

            CommandInfo cmdinf = null;
            List<CommandInfo> cmdList = new List<CommandInfo>();
            List<ProcCommandInfo> procList = new List<ProcCommandInfo>();

            if (sqlBattaryBuf != string.Empty)
            {
                cmdinf = new CommandInfo(sqlBattaryBuf, null);//电池缓存表
                cmdList.Add(cmdinf);
            } 
            if (sqlBattaryGrpBuf != string.Empty)
            {
                cmdinf = new CommandInfo(sqlBattaryGrpBuf, null);//电池组缓存表
                cmdList.Add(cmdinf);
            }            
            if (sqlUpdateIpAndOther != string.Empty)
            {
                cmdinf = new CommandInfo(sqlUpdateIpAndOther, null);//ip地址和其它信息
                cmdList.Add(cmdinf);
            }

            dataWarn = dataBatteryGrpWarn + WARN_DATA_DELIM + dataBatteryWarn;
            dataWarn = Regex.Replace(dataWarn, WARN_DATA_DELIM+"$", "");
            if (dataWarn.Trim() != null)
            {
                MySqlParameter MysqlPara ;
                MysqlPara = new MySqlParameter("p_warndata",MySqlDbType.VarChar,10000);
                MysqlPara.Value = dataWarn;

                procList.Add(new ProcCommandInfo("sp_SaveWarningInfo", new MySqlParameter[] { MysqlPara }));//警报信息
            }

            string ErrorMsg = "OK";
            try
            {
                //DbHelperSQL.connectionString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
                //DbHelper.ExecuteSqlTran(cmdList);
                DbHelper.ExecuteSqlTran(cmdList, procList);//在一个事务中，执行SQL语句、存储过程。
                Log2Database(xml, "OK");
            }
            catch (Exception ex)
            {
                ErrorMsg = ex.Message + " " + ex.StackTrace;
                throw;
            }                    
            return "OK"; 
        }
        private void Log2Database(string xml, string ErrorMsg)
        {
           bool isLog = !string.IsNullOrEmpty(ConfigurationManager.AppSettings["LogXmlDataFlag"])
                    && Boolean.Parse(ConfigurationManager.AppSettings["LogXmlDataFlag"]);
           if (isLog)
           {
               string SqlInsertXmlLog;
               DbParameter[] Paras;
               this.BuildInsertXmlLog(xml,ErrorMsg, out SqlInsertXmlLog, out Paras);
              // DbHelperSQL.ExecuteSql(SqlInsertXmlLog, Paras);
               DbHelper.ExecuteSql(SqlInsertXmlLog, Paras);
           }
        }
      
        /// <summary>
        /// 电池buf表（编制sql语句）
        /// </summary>
        /// <param name="Generation"></param>
        /// <param name="Mac"></param>
        /// <param name="Dtm"></param>
        /// <param name="xmlDoc"></param>
        /// <returns></returns>
        private void BuildBattarySQL(int Generation,string Mac,DateTime Dtm, XmlDocument xmlDoc
            , out string sqlBattaryBuf, out string BatteryGrpWarnData)
        {
            XmlNodeList nds = xmlDoc.SelectNodes("/data/r");//注意大小写
            XmlNode nd = null;
            int WarningCount = 0;
            if (nds.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
               // StringBuilder sb2 = new StringBuilder();   
                StringBuilder sbData = new StringBuilder();

                sb.Append(" Insert into S_BatBuffer(Generation,EquipID,BatteryGroup,BatteryID,Vval,Rval,Tval,Capacity,StatusNO,Status,LogTime)");
                //SELECT @general,@Mac,g,n,v,ir,t,cf,sn,s,@dtm FROM @temptbl
                for (int i = 0; i < nds.Count; i++)
                {
                    nd = nds[i];
                    //电池Buf
                    sb.AppendFormat(" select ");
                    sb.AppendFormat("{0},", Generation);//2代机
                    sb.AppendFormat("'{0}',", Mac);//mac地址
                    sb.AppendFormat("'{0}',", nd["g"].InnerText.Trim());//电池组号
                    sb.AppendFormat("'{0}',", nd["n"].InnerText.Trim());//电池号
                    sb.AppendFormat("'{0}',", nd["v"].InnerText.Trim());//电压
                    sb.AppendFormat("'{0}',", nd["ir"].InnerText.Trim());//内阻
                    sb.AppendFormat("'{0}',", nd["t"].InnerText.Trim());//温度
                    sb.AppendFormat("'{0}',", nd["cf"].InnerText.Trim());//剩余容量
                    sb.AppendFormat("'{0}',", nd["sn"].InnerText.Trim());//状态编号
                    sb.AppendFormat("'{0}',", nd["s"].InnerText.Trim());//状态文本
                    sb.AppendFormat("'{0}'", Dtm.ToString("yyyy-MM-dd HH:mm:ss"));//时间
                    sb.Append(" union all ");
                    //电池警报
                    if (nd["f"].InnerText.Trim() == "1")
                    {
                        /* sb2.AppendFormat(@" if not exists(select 1 from S_Warning where IsActive=1 and EquipID='{0}' and BatteryGroup='{1}' and BatteryID={2} and WarningNO='{3}')
                             insert into S_Warning(EquipID,BatteryGroup,BatteryID,WarningTime,WarningNO,WarningStatus) values('{0}','{1}',{2},'{4}','{3}','{5}') ;
                          else 
                           Update S_Warning set WarningTime='{4}' where IsActive=1 and EquipID='{0}' and BatteryGroup='{1}' and BatteryID={2} and WarningNO='{3}' ;"
                           , Mac, nd["g"].InnerText.Trim(), nd["n"].InnerText.Trim(), nd["sn"].InnerText.Trim(), Dtm.ToString(), nd["s"].InnerText.Trim());*/

                        //字段格式顺序：(EquipID,BatteryGroup,BatteryID,WarningTime,WarningNO,WarningStatus)                        
                        sbData.AppendFormat("{0}*{1}*{2}*{3}*{4}*{5}" + WARN_DATA_DELIM
                            , Mac, nd["g"].InnerText.Trim(), nd["n"].InnerText.Trim(), Dtm.ToString("yyyy-MM-dd HH:mm:ss"), nd["sn"].InnerText.Trim(), nd["s"].InnerText.Trim());//字段用*分割，记录用@@@@@分割。
                        WarningCount++;
                    }
                }
                sb.Length -= "union all ".Length;//除最后的union all。
                sqlBattaryBuf = sb.ToString();
                if (WarningCount > 0)
                {
                    sbData.Length -= WARN_DATA_DELIM.Length;
                    BatteryGrpWarnData = sbData.ToString();
                }
                else
                {
                    BatteryGrpWarnData = "";
                }                
            }
            else
            {
                sqlBattaryBuf = "";
                BatteryGrpWarnData = "";
            }
        }
        private void BuildBattaryGrpSQL(int Generation, string Mac, DateTime Dtm, XmlDocument xmlDoc
            , out string sqlBattaryGrpBuf, out string BatteryGrpWarnData)
        {
            XmlNodeList nds = xmlDoc.SelectNodes("/data/G");//注意大小写
            XmlNode nd = null;
            int WarningCount = 0;
            string DBType = ConfigurationManager.AppSettings["DBType"];
            if (nds.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                StringBuilder sbData = new StringBuilder();
               
                sb.Append(" Insert into S_BatgrpBuffer(Generation,EquipID,BatteryGroup,Vval,Ival,CdFree,CdTime,CapFree,TimeFree,WorkStatus,WarningContent,Status,WarningContentDescr,BoundaryInfo,LogTime)");
                
                //SELECT @general,@Mac,g,n,v,ir,t,cf,sn,s,@dtm FROM @temptbl
                for (int i = 0; i < nds.Count; i++)
                {
                    nd = nds[i];
                    //电池组Buf
                    sb.AppendFormat(" select ");
                    sb.AppendFormat("{0},", Generation);//2代机
                    sb.AppendFormat("'{0}',", Mac);//mac地址
                    sb.AppendFormat("'{0}',", nd["g"].InnerText.Trim());//电池组号                  
                    sb.AppendFormat("'{0}',", nd["v"].InnerText.Trim());//总电压
                    sb.AppendFormat("'{0}',", nd["i"].InnerText.Trim());//总电流
                    sb.AppendFormat("'{0}',", nd["iocp"].InnerText.Trim());//充放容量
                    sb.AppendFormat("'{0}',", nd["iotl"].InnerText.Trim());//充放时长

                    sb.AppendFormat("'{0}',", nd["cf"].InnerText.Trim());//剩余容量
                    sb.AppendFormat("'{0}',", nd["tf"].InnerText.Trim());//剩余可用时间

                    sb.AppendFormat("'{0}',", nd["sn"].InnerText.Trim());//状态编号
                    sb.AppendFormat("'{0}',", nd["en"].InnerText.Trim());//错误编号

                    sb.AppendFormat("'{0}',", nd["s"].InnerText.Trim());//状态文本                   
                    sb.AppendFormat("'{0}',", nd["e"].InnerText.Trim());//错误文本

                    sb.AppendFormat("'{0}',", nd["boundary"].OuterXml.Trim());//边界值xml
                    sb.AppendFormat("'{0}'", Dtm.ToString("yyyy-MM-dd HH:mm:ss"));//
                    sb.Append(" union all ");
                    //电池组警报
                    if (nd["f"].InnerText.Trim() == "1")
                    {
                        /*
                        sb2.AppendFormat(@" if not exists(select 1 from S_Warning where IsActive=1 and EquipID='{0}' and BatteryGroup='{1}' and BatteryID=-1 and WarningNO='{2}')
                            insert into S_Warning(EquipID,BatteryGroup,BatteryID,WarningTime,WarningNO,WarningStatus) values('{0}','{1}',-1,'{3}','{2}','{4}') ;
                         else 
                          Update S_Warning set WarningTime='{3}' where IsActive=1 and EquipID='{0}' and BatteryGroup='{1}' and BatteryID=-1 and WarningNO='{2}' ;"
                          , Mac, nd["g"].InnerText.Trim(), nd["en"].InnerText.Trim(), Dtm.ToString(), nd["e"].InnerText.Trim());*/
                        
                        //字段格式顺序：(EquipID,BatteryGroup,BatteryID,WarningTime,WarningNO,WarningStatus)
                        sbData.AppendFormat("{0}*{1}*{2}*{3}*{4}*{5}" + WARN_DATA_DELIM
                            , Mac, nd["g"].InnerText.Trim(), -1, Dtm.ToString("yyyy-MM-dd HH:mm:ss"), nd["en"].InnerText.Trim(), nd["e"].InnerText.Trim());//字段用*分割，记录用@@@@@分割。
                        WarningCount ++;
                    }
                }
                sb.Length -= "union all ".Length;//除最后的union all。
                sqlBattaryGrpBuf = sb.ToString();
                if (WarningCount > 0)
                {
                    sbData.Length -= WARN_DATA_DELIM.Length;//除最后的@@@@@。\
                    BatteryGrpWarnData = sbData.ToString();
                }
                else
                {
                    BatteryGrpWarnData = "";
                }
            }
            else
            {
                sqlBattaryGrpBuf = "";
                BatteryGrpWarnData = "";
            }
        }
        private void BuildLanUpdateIpSQL( string Mac,string Ip, DateTime Dtm, XmlDocument xmlDoc
         , out string sqlUpdateIpAndOther)
        {
            //
            XmlNodeList nds = xmlDoc.SelectNodes("/data/P");//注意大小写
            XmlNode nd = null;
          
            if (nds.Count > 0)
            {
                sqlUpdateIpAndOther = "";
                nd = nds[0];
                if (DBType == ConstSet.DBTYPE_MYSQL)
                {
                    sqlUpdateIpAndOther = string.Format(" call sp_SaveHostIPMac ('{0}','{1}','{2}','{3}','{4}') ;"
                     , Mac, Ip, Dtm.ToString("yyyy-MM-dd HH:mm:ss"), nd["ent"].InnerText.Trim(), nd["cd"].InnerText.Trim());
                }
                else if (DBType == ConstSet.DBTYPE_MSSQL)
                {
                    sqlUpdateIpAndOther = string.Format(" exec sp_SaveHostIPMac @Mac='{0}',@IP='{1}',@dtm='{2}',@ent='{3}',@cd='{4}' ;"
                    , Mac, Ip, Dtm.ToString("yyyy-MM-dd HH:mm:ss"), nd["ent"].InnerText.Trim(), nd["cd"].InnerText.Trim());
                }
               

            }
            else
            {
                sqlUpdateIpAndOther = "";
            }
        }
        private void BuildInsertXmlLog(string xml, string errorMsg, out string sqlInsertXmlLog, out DbParameter[] paras)
        {
            //-- 插入xml日志
            //insert into dbo.S_Xml(DataXml,Error) values(@xmldata,@errorMsg);
            sqlInsertXmlLog = "INSERT INTO S_Xml(DataXml,ErrorMsg) VALUES(@xmldata,@errorMsg);";
            if (DBType == ConstSet.DBTYPE_MYSQL)
            {
                paras = new DbParameter[2];
                (paras[0] = new MySqlParameter("@xmldata", MySqlDbType.VarChar, 4000)).Value = xml;
                (paras[1] = new MySqlParameter("@errorMsg", MySqlDbType.VarChar, 500)).Value = errorMsg;

            }
            else if (DBType == ConstSet.DBTYPE_MSSQL)
            {
                paras = new DbParameter[2];
                (paras[0] = new SqlParameter("@xmldata", SqlDbType.NVarChar, 4000)).Value = xml;
                (paras[1] = new SqlParameter("@errorMsg", SqlDbType.NVarChar, 500)).Value = errorMsg;
            }
            else
            {
                sqlInsertXmlLog = null;
                paras = null;
            }

        }

    }
}
